USE [SCHOOL]
GO

/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_ActualizarAlumno]    Script Date: 12/07/2011 00:14:42 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ALUMNO_ActualizarAlumno]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_ALUMNO_ActualizarAlumno]
GO

USE [SCHOOL]
GO

/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_ActualizarAlumno]    Script Date: 12/07/2011 00:14:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 
-- Description:	Actualiza un alumno
-- =============================================
CREATE PROCEDURE [dbo].[usp_ALUMNO_ActualizarAlumno]
	@intPersonaId		INT,
	@varNombre			VARCHAR(250),
	@varTipo			VARCHAR(10),
	@varApellidoPaterno	VARCHAR(250),
	@varApellidoMaterno	VARCHAR(250),
	@varCedula			VARCHAR(30),
	@varDireccion		VARCHAR(500),
	@varTelefono		VARCHAR(50),
	@varEmail			VARCHAR(250),
	@varFechaNacimiento	DATE,
	@varLugarNacimiento	VARCHAR(250),
	@varCelular			VARCHAR(50),
	@varColegioAnterior	VARCHAR(500),
	@varRegistro		VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;	
	
	BEGIN TRANSACTION
	
	BEGIN TRY
	
		UPDATE [SCHOOL].[dbo].[tbl_Persona]
		   SET [nombre] = @varNombre
			  ,[tipo] = @varTipo
			  ,[apellidoPaterno] = @varApellidoPaterno
			  ,[apellidoMaterno] = @varApellidoMaterno
			  ,[cedula] = @varCedula
			  ,[direccion] = @varDireccion
			  ,[telefono] = @varTelefono
			  ,[email] = @varEmail
			  ,[fechaNacimiento] = @varFechaNacimiento
			  ,[lugarNacimiento] = @varLugarNacimiento
			  ,[celular] = @varCedula
		 WHERE [personaId] = @intPersonaId


		
		UPDATE [dbo].[tbl_Estudiante]
		SET [colegioAnterior] = @varColegioAnterior
		  ,[registro] = @varRegistro
		WHERE [personaId] = @intPersonaId
    
		COMMIT TRANSACTION
		
    END TRY
    BEGIN CATCH
		
		ROLLBACK TRANSACTION
    
    END CATCH
    
END

GO




/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_EliminarAlumno]    Script Date: 12/07/2011 00:23:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ALUMNO_EliminarAlumno]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_ALUMNO_EliminarAlumno]
GO


/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_EliminarAlumno]    Script Date: 12/07/2011 00:23:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 
-- Description:	Elimina un alumno
-- =============================================
CREATE PROCEDURE [dbo].[usp_ALUMNO_EliminarAlumno]
	@intPersonaId		INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;	
	
	BEGIN TRANSACTION
	
	BEGIN TRY
	
		DELETE
		FROM [dbo].[tbl_Estudiante]
		WHERE [personaId] = @intPersonaId
		
		DELETE
		FROM [dbo].[tbl_Persona]
		WHERE [personaId] = @intPersonaId
		
		
    
		COMMIT TRANSACTION
		
    END TRY
    BEGIN CATCH
		
		ROLLBACK TRANSACTION
    
    END CATCH
    
END

GO




/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_GetAlumnoById]    Script Date: 12/07/2011 00:24:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ALUMNO_GetAlumnoById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_ALUMNO_GetAlumnoById]
GO

/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_GetAlumnoById]    Script Date: 12/07/2011 00:24:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 
-- Description:	Obtiene un alumno
-- =============================================
CREATE PROCEDURE [dbo].[usp_ALUMNO_GetAlumnoById]
	@intPersonaId		INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;	
	
	SELECT p.[personaId]
      ,p.[nombre]
      ,p.[tipo]
      ,p.[apellidoPaterno]
      ,p.[apellidoMaterno]
      ,p.[cedula]
      ,p.[direccion]
      ,p.[telefono]
      ,p.[email]
      ,p.[fechaNacimiento]
      ,p.[lugarNacimiento]
      ,p.[celular]
      ,e.[colegioAnterior]
      ,e.[registro]
	FROM	[dbo].[tbl_Persona] p , [dbo].[tbl_Estudiante] e
	WHERE	e.[personaId] = p.[personaId]
			AND p.[personaId] = @intPersonaId

    
END

GO





/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_GetAlumnoBySearch]    Script Date: 12/07/2011 00:24:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ALUMNO_GetAlumnoBySearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_ALUMNO_GetAlumnoBySearch]
GO

/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_GetAlumnoBySearch]    Script Date: 12/07/2011 00:24:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 
-- Description:	Obtiene un alumno
-- =============================================
CREATE PROCEDURE [dbo].[usp_ALUMNO_GetAlumnoBySearch]
	@varSearchTerm VARCHAR(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;	
	
	SELECT p.[personaId]
      ,p.[nombre]
      ,p.[tipo]
      ,p.[apellidoPaterno]
      ,p.[apellidoMaterno]
      ,p.[cedula]
      ,p.[direccion]
      ,p.[telefono]
      ,p.[email]
      ,p.[fechaNacimiento]
      ,p.[lugarNacimiento]
      ,p.[celular]
      ,e.[colegioAnterior]
      ,e.[registro]
	FROM	[dbo].[tbl_Persona] p 
			JOIN [dbo].[tbl_Estudiante] e ON e.[personaId] = p.[personaId]
	WHERE	p.[nombre] LIKE '%' + @varSearchTerm  + '%'
			OR p.[apellidoPaterno] LIKE '%' + @varSearchTerm  + '%'
			OR p.[apellidoPaterno] LIKE '%' + @varSearchTerm  + '%'
			OR p.[cedula] LIKE '%' + @varSearchTerm  + '%'
			OR e.[registro]  LIKE '%' + @varSearchTerm  + '%'

    
END

GO



/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_InsertarAlumno]    Script Date: 12/07/2011 00:24:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ALUMNO_InsertarAlumno]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_ALUMNO_InsertarAlumno]
GO

/****** Object:  StoredProcedure [dbo].[usp_ALUMNO_InsertarAlumno]    Script Date: 12/07/2011 00:24:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 
-- Description:	Inserta un alumno
-- =============================================
CREATE PROCEDURE [dbo].[usp_ALUMNO_InsertarAlumno]
	@intPersonaId		INT	OUTPUT,
	@varNombre			VARCHAR(250),
	@varTipo			VARCHAR(10),
	@varApellidoPaterno	VARCHAR(250),
	@varApellidoMaterno	VARCHAR(250),
	@varCedula			VARCHAR(30),
	@varDireccion		VARCHAR(500),
	@varTelefono		VARCHAR(50),
	@varEmail			VARCHAR(250),
	@varFechaNacimiento	DATE,
	@varLugarNacimiento	VARCHAR(250),
	@varCelular			VARCHAR(50),
	@varColegioAnterior	VARCHAR(500),
	@varRegistro		VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;	
	
	BEGIN TRANSACTION
	
	BEGIN TRY
	
		INSERT INTO [dbo].[tbl_Persona]
			   ([nombre]
			   ,[tipo]
			   ,[apellidoPaterno]
			   ,[apellidoMaterno]
			   ,[cedula]
			   ,[direccion]
			   ,[telefono]
			   ,[email]
			   ,[fechaNacimiento]
			   ,[lugarNacimiento]
			   ,[celular])
		 VALUES
			   (@varNombre
			   ,@varTipo
			   ,@varApellidoPaterno
			   ,@varApellidoMaterno
			   ,@varCedula
			   ,@varDireccion
			   ,@varTelefono
			   ,@varEmail
			   ,@varFechaNacimiento
			   ,@varLugarNacimiento
			   ,@varCedula)
	           
		SET @intPersonaId = SCOPE_IDENTITY()
		
		INSERT INTO [dbo].[tbl_Estudiante]
           ([colegioAnterior]
           ,[registro])
		VALUES
		   (@varColegioAnterior
		   ,@varRegistro)
    
		COMMIT TRANSACTION
		
    END TRY
    BEGIN CATCH
		
		ROLLBACK TRANSACTION
    
    END CATCH
    
END

GO

